package DBLayer;
import java.sql.*;
import java.util.ArrayList;
import ModelLayer.*;

public class DBGunReplica implements IFDBGunReplicas
{
	private Connection db_con;
	
	public DBGunReplica()
	{
		 db_con = DBConnection1.getInstance().getDBcon();
	}
	
	private GunReplicas buildGunReplica(ResultSet results)	
    {
		GunReplicas replicaObj=new GunReplicas();		
		
		try
		{
			replicaObj.setID(results.getInt(1));
			replicaObj.setFabric(results.getString(2));
			replicaObj.setCalibre(results.getFloat(3));
			System.out.println("Build gun replica" + replicaObj.getName());
		}
		catch (Exception e)
        {
            System.out.println("error in building the gun replica object");
        }
        
        return replicaObj;
    }
	
	private String buildQuery(String wClause)
    {
        String query = "SELECT * FROM Product as p JOIN GunReplicas as g ON p.ProdID=g.ProdID";
        
        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }
        
        return query;
    }
	
	//use singleWhere to select only one gun replica at a time
    private GunReplicas singleWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        GunReplicas replicaObj = new GunReplicas();
        
        String query = buildQuery(wClause);
        System.out.println(query);
        
        try
        {// read the replica from the database
        	Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            
            if (results.next())
            {
                replicaObj = buildGunReplica(results);
                stmt.close();
                if(retrieveAssociation)
                {//the supplier is to be built as well
                	IFDBSupplier dbSupplier=new DBSupplier();
                	Supplier suppObj=dbSupplier.getSupplierByName(replicaObj.getSupplier().getName(), false);
                	System.out.println("Supplier is selected.");
                	replicaObj.setSupplier(suppObj); //supplier is selected
                }
                else
                {
                	replicaObj=null;
                }
            }
        }
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return replicaObj;
    }
    
  //use miscWhere to select multiple products at a time
    private ArrayList<GunReplicas> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<GunReplicas> replicaList = new ArrayList<GunReplicas>();

        String query = buildQuery(wClause);

        try 
        {
        	Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            
            while (results.next())
            {
                GunReplicas replicaObj = new GunReplicas();
                replicaObj = buildGunReplica(results);
                replicaList.add(replicaObj);
            }
            stmt.close();
            if(retrieveAssociation)
            {//the supplier is to be built as well
            	IFDBSupplier dbSupp=new DBSupplier();
            	for(GunReplicas replicaObj : replicaList)
            	{
            		int SuppID=replicaObj.getSupplier().getId();
            		Supplier suppObj=dbSupp.getSuppById(SuppID, false);
            		replicaObj.setSupplier(suppObj);
            		System.out.println("Supplier is selected.");
                }
            }
        }
        catch (Exception e)
        {
        	System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return replicaList;
    }
	
	@Override
	public ArrayList<GunReplicas> getAllGunReplicas(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public GunReplicas getGunReplicaByID(int id, boolean retrieveAssociation)
	{
		String wClause = " p.ProdID= '" + id + "'";
        return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public ArrayList<GunReplicas> searchByFabric(String fabric, boolean retrieveAssociation)
	{
		String wClause=" g.Fabric like '%" + fabric + "%'";
		return miscWhere(wClause, retrieveAssociation);
	}

	@Override
	public ArrayList<GunReplicas> searchByCalibre(float calibre, boolean retrieveAssociation)
	{
		String wClause=" g.Calibre= " + calibre + "'";
		return miscWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertGunReplica(GunReplicas replica) throws Exception
	{
		int result = -1;
        String query = "INSERT INTO GunReplicas(Fabric, Calibre) VALUES ('" + 
		//replica.getID() + "', '" +
        		replica.getFabric() + "', '" +
		replica.getCalibre() + "')";
        
        System.out.println("insert: " + query);
        
        try
        {
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);
            stmt.close();
        }
        
        catch (SQLException ex)
        {
            System.out.println("Gun replica not created.");
            throw new Exception("Gun replica has not been inserted correctly.");
        }
        return result;
	}

	@Override
	public int updateGunReplicaByID(GunReplicas replica)
	{
		GunReplicas replicaObj=replica;
		
		int result=-1;
		
		String query= "UPDATE GunReplicas SET " +
		"Fabric= '" + replicaObj.getFabric() + "' " +
		"Calibre= '" + replicaObj.getCalibre() + "'" +
		"WHERE ProdID= '" + replicaObj.getID() + "'";
		System.out.println("Update query:" + query);
		
		try
		{
			Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	result = stmt.executeUpdate(query);
	 	 	stmt.close();
		}
		
		catch(Exception ex)
		{
	 	 	System.out.println("Update exception in GunReplicas db: "+ex);
	  	}
		return(result);
	}

	@Override
	public int deleteGunReplicaByID(int id)
	{
		int result = -1;
    	String query = "DELETE FROM GunReplicas WHERE ProdID= '" + id + "'";
        System.out.println(query);
        try
        {//delete from Gun replicas a replica
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception ex)
        {
            System.out.println("Delete exception in GunReplicas db: " + ex);
        }
        return result;
	}
}
